#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--客户意向统计宽表
insert into hive.edu_dm.dm_customer_relationship
select
    '2023-05-08' as date_time,
    year_code,
    year_month,
    year_month_day,

    case when grouping(year_month_day) = 0
         then 'date'
         when grouping(year_month) = 0
         then 'month'
         when grouping(year_code) = 0
         then 'year'
         end as time_type,

    case when grouping(area) = 0
         then 'area'
         when grouping(sub_id) = 0
         then 'subject'
         when grouping(school_id) = 0
         then 'school'
         when grouping(origin_channel) = 0
         then 'origin_channel'
         when grouping(depart_id) = 0
         then 'depart'
         else 'all'
         end as group_type,
    area,
    depart_id,
    depart_name,
    clue_state,
    origin_type,
    sub_id,
    sub_name,
    school_id,
    school_name,
    origin_channel
-- 统计值
    ,sum(id_count) as id_count
from hive.edu_dws.dws_customer_relationship_daycout
group by
grouping sets (
    --年
    (year_code, origin_type, clue_state ),
    (year_code, origin_type, clue_state, depart_id, area),
    (year_code, origin_type, clue_state, depart_id, sub_id, sub_name),
    (year_code, origin_type, clue_state, depart_id, school_id, school_name),
    (year_code, origin_type, clue_state, depart_id, origin_channel),
    (year_code, origin_type, clue_state, depart_id, depart_name),
    --月
    (year_code, year_month, origin_type, clue_state ),
    (year_code, year_month, origin_type, clue_state, depart_id, area),
    (year_code, year_month, origin_type, clue_state, depart_id, sub_id, sub_name),
    (year_code, year_month, origin_type, clue_state, depart_id, school_id, school_name),
    (year_code, year_month, origin_type, clue_state, depart_id, origin_channel),
    (year_code, year_month, origin_type, clue_state, depart_id, depart_name),
    --日
    (year_code, year_month, year_month_day, origin_type, clue_state ),
    (year_code, year_month, year_month_day, origin_type, clue_state, depart_id, area),
    (year_code, year_month, year_month_day, origin_type, clue_state, depart_id, sub_id, sub_name),
    (year_code, year_month, year_month_day, origin_type, clue_state, depart_id, school_id, school_name),
    (year_code, year_month, year_month_day, origin_type, clue_state, depart_id, origin_channel),
    (year_code, year_month, year_month_day, origin_type, clue_state, depart_id, depart_name)
);


--客户线索宽表
insert into hive.edu_dm.dm_customer_clue
select
    '2023-05-08' as date_time,
    group_type,
    clue_state,
    origin_type,
    appeal_status,
    datetime,
    dt,
    first_id_effective_count,
    first_id_count
from hive.edu_dws.dws_customer_clue_daycout;
"